package io.renren.modules.sys.controller;

import io.renren.common.utils.PageUtils;
import io.renren.common.utils.R;
import io.renren.common.validator.ValidatorUtils;
import io.renren.modules.sys.dao.ColumnsInfoDao;
import io.renren.modules.sys.entity.DatabaseinfoEntity;
import io.renren.modules.sys.entity.TableInfoEntity;
import io.renren.modules.sys.service.DatabaseinfoService;
import io.renren.modules.sys.service.TableInfoService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.web.bind.annotation.*;

import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.List;
import java.util.Map;


/**
 * 数据源管理
 *
 * @author zhangfn
 * @date 2020-06-02 01:08:50
 */
@RestController
@RequestMapping("sys/databaseinfo")
public class DatabaseinfoController {
    @Autowired
    private DatabaseinfoService databaseinfoService;

    @Autowired
    private TableInfoService tableInfoService;

    /**
     * 列表
     */
    @RequestMapping("/list")
    public R list(@RequestParam Map<String, Object> params) {
        PageUtils page = databaseinfoService.queryPage(params);

        return R.ok().put("page", page);
    }

    /**
     * 数据源下拉框展示
     *
     * @return
     */
    @RequestMapping("/querySelect")
    public R querySelect() {
        List<DatabaseinfoEntity> list = databaseinfoService.queryAll();
        return R.ok().put("list", list);
    }


    /**
     * 信息
     */
    @RequestMapping("/info/{id}")
//    @RequiresPermissions("sys:databaseinfo:info")
    public R info(@PathVariable("id") Integer id) {
        DatabaseinfoEntity databaseinfo = databaseinfoService.getById(id);
        return R.ok().put("databaseinfo", databaseinfo);
    }

    /**
     * 保存
     */
    @RequestMapping("/save")
//    @RequiresPermissions("sys:databaseinfo:save")
    public R save(@RequestBody DatabaseinfoEntity databaseinfo) {
        databaseinfoService.save(databaseinfo);
        return R.ok();
    }

    /**
     * 修改
     */
    @RequestMapping("/update")
//    @RequiresPermissions("sys:databaseinfo:update")
    public R update(@RequestBody DatabaseinfoEntity databaseinfo) {
        ValidatorUtils.validateEntity(databaseinfo);
        databaseinfoService.updateById(databaseinfo);
        return R.ok();
    }

    /**
     * 删除
     */
    @RequestMapping("/delete")
//    @RequiresPermissions("sys:databaseinfo:delete")
    public R delete(@RequestBody Integer[] ids) {
        databaseinfoService.removeByIds(Arrays.asList(ids));
        return R.ok();
    }


    /**
     * 测试数据库链接是否正常，并打印数据库的版本信息
     */
    @RequestMapping("/checkDatabase")
    public R testDBConnection(@RequestBody DatabaseinfoEntity databaseinfo) {
        String msg = "";
        try {
            Class.forName(databaseinfo.getDrivername());
            Connection conn = null;
            conn = DriverManager.getConnection(databaseinfo.getUrl(), databaseinfo.getUsername(), databaseinfo.getPassword());
            DatabaseMetaData metaData = conn.getMetaData();
            msg = "测试成功！连接的数据库为：" + metaData.getDatabaseProductName() + "-" + metaData.getDatabaseProductVersion();
            conn.close();
        } catch (Exception e) {
            System.out.println("数据库连接失败！");

            msg = "测试失败！" + e.getMessage();
            System.out.println("1:" + e.getMessage());
            System.out.println("3:" + e.getLocalizedMessage());
            return R.error(msg);
        }

        return R.ok(msg);
    }


    /**
     * 根据选择的数据源，查询此数据源下所有的表，插入到数据库中
     *
     * @param connid
     * @return
     */
    @RequestMapping(value = "/createTableInfo", method = RequestMethod.POST)
    @Transactional
    public R createTableInfo(@RequestParam String connid) {
        DatabaseinfoEntity databaseinfoEntity = new DatabaseinfoEntity();
        if (null != connid && !connid.equals("")) {
            databaseinfoEntity = databaseinfoService.queryById(connid);
        }

        List<TableInfoEntity> tables = new ArrayList<>();
        if (null != databaseinfoEntity.getConnname() && !databaseinfoEntity.getConnname().equals(null)) {
            Connection conn = null;
            try {
                Class.forName(databaseinfoEntity.getDrivername());
                conn = DriverManager.getConnection(databaseinfoEntity.getUrl()
                        , databaseinfoEntity.getUsername(), databaseinfoEntity.getPassword());

                //按照不同的数据库类型进行查询
                switch (databaseinfoEntity.getDatasourcetype()) {
                    case "mysql":
                        tables = tableInfoService.mysqlQueryTable(conn, tables, databaseinfoEntity, connid);
                        break;
                    case "postgresql":
                        tables = tableInfoService.pgsqlQueryTable(conn, tables, databaseinfoEntity, connid);
                        break;
                    case "oracle":
                        tables = tableInfoService.oracleQueryTable(conn, tables, databaseinfoEntity, connid);
                        break;
                    default:
                }
            } catch (Exception e) {
                e.printStackTrace();
            } finally {
                try {
                    conn.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }

        if (tables.size() > 0) {
            tableInfoService.delTables(connid);

            for (TableInfoEntity table : tables) {
                tableInfoService.save(table);
//                columnsInfoDao.delColumns(table.getId().toString());
            }

        }

        return R.ok();
    }


}
